[初心者向け]BigQueryのワイルドカードテーブルを試してみた

データアナリティクス事業本部の根本です。根本がBig Queryを使っていて面白い機能だなと思った機能として[ワイルドカードテーブル]という機能があります。最近触る機会があったので記事にしてみました。

この記事の対象者

• Big Queryのワイルドカードテーブルを使ってみたいひと、知りたいひと

前提条件

• Big Query APIが有効化されていること

ワイルドカードテーブルとは

BigQueryのワイルドカードテーブル機能は、特定のパターンにマッチする複数のテーブルをクエリの対象として指定することができる機能です。
日付に基づいて作成されているテーブル(log_20240401,log_20240402のような)や特定の共通パターンを持ったテーブルが生成されている場合に便利です。

※2024/5/7現在では[log_20240401,log_20240402]のような日付毎のシャーディングテーブルは推奨されておらずパーティショニングを使用した単一テーブルの使用が推奨されています。本記事はワイルドカードテーブルの機能検証に焦点を当てておりますのでこの点に関してご注意ください。

事前準備

以下の2テーブルを作成しました。
テーブル名：test_20240501
列名：table_name
列の値：test_20240501

テーブル名：test_20240502
列名：table_name
列の値：test_20240502
以下のようなテーブルです。

ワイルドカードテーブルの使い方

ワイルドカードテーブルを使用する際は、FROM句内でテーブル名の一部にアスタリスク（*）を使用して、マッチする複数のテーブルを指定します。たとえば、test_20240501、test_20240502というテーブルの場合、以下のようにクエリを書くことで、これらのテーブル全てを一度にクエリすることができます。

SELECT *
FROM `プロジェクトID.データセット名.test_*`

上記クエリはワイルドカードテーブルを使わない場合だと以下のようにUNION句で実装する必要があります。

SELECT table_name FROM `プロジェクト名.データセット名.test_20240501`
UNION ALL
SELECT table_name FROM `プロジェクト名.データセット名.test_20240502`

対象テーブル数が増えれば増えるほど、効果は大きくなります。
また、ワイルドカードテーブルを用いると、クエリ結果の各行に_TABLE_SUFFIXという列が自動で付与されます。
test_20240501テーブルはtable_nameという名前の列しか持っていないテーブルなのですが、ワイルドカードテーブルを用いてクエリを実行すると_TABLE_SUFFIX列が自動でクエリ結果に付与されます(明示的に指定しないと結果は出力されません)。
実際に以下のクエリを実行して確認してみました。

SELECT *, _TABLE_SUFFIX FROM `プロジェクト名.データセット名.test_*`

table_nameという列しかないテーブルなのですが、SELECT句に_TABLE_SUFFIX列を指定してクエリが実行でき、値も出力されているのが確認できます。
ワイルドカードテーブルは、この_TABLE_SUFFIXの値を対象にWHERE句で条件設定をして対象テーブルを絞り込むこともできます。
以下のクエリは_TABLE_SUFFIXで20240501を含むテーブルを指定したクエリとなります。

SELECT *, _TABLE_SUFFIX FROM `プロジェクト名.データセット名.test_*`
WHERE _TABLE_SUFFIX IN('20240501')

ワイルドカードテーブル機能は以下のようなメリットがあると考えます。

• 特定の日付範囲やパターンに合致するテーブル群を簡単にクエリできる
• クエリがシンプルになる(複数のテーブル名を個別に指定する必要がなく、ワイルドカードを使って一括で指定できるから)

注意点

リファレンスを見ていたところ、いくつか注意点がありそうでした。

• Viewをサポートしていない(Viewがクエリ結果に含まれる場合はエラーとなる)
• キャッシュに保存された結果はサポートされていない

など。他にも注意点があるので使用する場合はリファレンスを一度目を通した方が良いと思います。
詳しくはリファレンスの制限事項を参照ください。

おわりに

日単位で作成しているテーブルをさくっとクエリしたい、という場合に効果を発揮すると思いました。今まで使っていたツールではこのような機能はなかったので、とても面白く役立つ機能だなと思っています。
この記事がどなたかのお役に立てば嬉しいです。それではまた。

参考

ワイルドカード テーブルを使用した複数テーブルに対するクエリ